﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace YN.Utils
{
    /// <summary>
    /// MS SQL SERVER 数据库访问类
    /// </summary>
    public class SQLHelper 
    {
        #region 数据库连接字符串

        private static string ConString;
        public static string ConnectionString
        {
            get
            {
                return ConString;
            }
        }
        #endregion

        #region 静态构造器

        static SQLHelper()
        {
            ConString = ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;
        }
        #endregion

        #region 创建一个新的数据库连接对象

        public static SqlConnection CreateConnection()
        {
            return new SqlConnection(ConString);
        }

        #endregion

        #region 执行SQL语句,返回受影响的记录行数

        public static int ExecuteNonQuery(string cmdText, SqlParameter[] param, CommandType cmdType)
        {
            using (SqlConnection con = CreateConnection())
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, con))
                {
                    cmd.CommandType = cmdType;

                    if (param != null)
                    {
                        foreach (SqlParameter pa in param)
                        {
                            cmd.Parameters.Add(pa);
                        }
                    }

                    try
                    {
                        con.Open();
                        int rows = cmd.ExecuteNonQuery();
                        con.Close();
                        cmd.Parameters.Clear();

                        return rows;
                    }
                    catch (SqlException)
                    {
                        return -1;
                    }
                }
            }
        }

        public static int ExecuteNonQuery(string sql, params SqlParameter[] param)
        {
            return ExecuteNonQuery(sql, param, CommandType.Text);
        }

        #endregion

        #region 执行Sql语句,返回查询结果(object)

        public static object ExecuteScalar(string cmdText, SqlParameter[] param, CommandType cmdType)
        {
            using (SqlConnection con = CreateConnection())
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, con))
                {
                    cmd.CommandType = cmdType;

                    if (param != null)
                    {
                        foreach (SqlParameter pa in param)
                        {
                            cmd.Parameters.Add(pa);
                        }
                    }

                    try
                    {
                        con.Open();
                        object obj = cmd.ExecuteScalar();
                        con.Close();
                        cmd.Parameters.Clear();

                        if (obj == DBNull.Value)
                            obj = null;

                        return obj;
                    }
                    catch (SqlException)
                    {
                        return null;
                    }
                }
            }
        }

        public static object ExecuteScalar(string sql, params SqlParameter[] param)
        {
            return ExecuteScalar(sql, param, CommandType.Text);
        }

        #endregion

        #region 执行查询语句，返回SqlDataReader

        public static SqlDataReader ExecuteReader(string cmdText, SqlParameter[] param, CommandType cmdType)
        {
            SqlConnection con = CreateConnection();
            using (SqlCommand cmd = new SqlCommand(cmdText, con))
            {
                cmd.CommandType = cmdType;

                if (param != null)
                {
                    foreach (SqlParameter pa in param)
                    {
                        cmd.Parameters.Add(pa);
                    }
                }

                try
                {
                    con.Open();
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch (SqlException)
                {
                    if (con.State == ConnectionState.Open)
                        con.Close();

                    return null;
                }
            }
        }

        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] param)
        {
            return ExecuteReader(sql, param, CommandType.Text);
        }
        #endregion

        #region 执行查询语句，返回DataTable

        public static DataTable Query(string cmdText, SqlParameter[] param, CommandType cmdType)
        {
            DataTable dt = new DataTable();

            using (SqlConnection con = CreateConnection())
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmdText, con))
                {
                    da.SelectCommand.CommandType = cmdType;

                    if (param != null)
                    {
                        foreach (SqlParameter pa in param)
                        {
                            da.SelectCommand.Parameters.Add(pa);
                        }
                    }

                    try
                    {
                        da.Fill(dt);
                    }
                    catch (SqlException)
                    {
                        return null;
                    }
                }
            }

            return dt;
        }

        public static DataTable Query(string sql, params SqlParameter[] param)
        {
            return Query(sql, param, CommandType.Text);
        }

        #endregion

        #region 执行数据库事务

        public static bool ExecuteTransaction(string[] sqls)
        {
            using (SqlConnection con = CreateConnection())
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    try
                    {
                        con.Open();
                        cmd.Transaction = con.BeginTransaction();

                        foreach (string sql in sqls)
                        {
                            if (!string.IsNullOrEmpty(sql))
                            {
                                cmd.CommandText = sql;
                                cmd.ExecuteNonQuery();
                            }
                        }

                        cmd.Transaction.Commit();
                        con.Close();

                        return true;
                    }
                    catch(SqlException)
                    {
                        cmd.Transaction.Rollback();

                        return false;
                    }
                }
            }
        }

        #endregion

        #region 创建SqlParameter对象

        public static SqlParameter CreateParameter(string paramName, SqlDbType type, object paramValue)
        {
            SqlParameter param = new SqlParameter(paramName, type);
            param.Value = paramValue;

            return param;
        }

        public static SqlParameter CreateParameter(string paramName, SqlDbType type, int size, object paramValue)
        {
            SqlParameter param = new SqlParameter(paramName, type, size);
            param.Value = paramValue;

            return param;
        }
        #endregion
    }
}
